package com.loongtech.bi.action.system;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.annotation.PostConstruct;
import javax.faces.application.FacesMessage;
import javax.faces.context.FacesContext;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Controller;

import com.loongtech.bi.action.BaseBiSearchAction;
import com.loongtech.bi.manager.flow.HadoopQueryManager;
import com.loongtech.bi.manager.system.SysLogManager;
import com.loongtech.bi.support.Session;

/**
 * 通用SQL查询
 * @author xckuang
 * @date 2015年2月26日
 */
@Controller("sqlQueryAction")
@Scope("view")
public class SqlQueryAction extends BaseBiSearchAction{
	private static final long serialVersionUID = 1L;
	private static final String	eFailTitle	= "通用查询执行错误";
	@Autowired
	private Session				session;
	@Autowired
	private SysLogManager		sysLogManager;
	@Autowired
	HadoopQueryManager hadoopQueryManager;
	private String				sql;
	private boolean				hasData;
	private List<Object[]>		dataList	= null;
	private List<String>		columnNames	= null;

	@PostConstruct
	private void init()
	{
		this.sql = "";
		this.hasData = false;
		this.dataList = new ArrayList<Object[]>(0);
		this.columnNames = new ArrayList<String>(0);
	}

	public void search()
	{
		this.hasData = false;
		this.columnNames.clear();
		this.dataList.clear();
		FacesContext context = FacesContext.getCurrentInstance();
		if (null == sql || sql.trim().isEmpty())
		{
			context.addMessage(null, new FacesMessage(eFailTitle, "查询语句为空"));
			return;
		}
		
		// 限制不允许insert update delete
		sql = sql.toLowerCase().trim();
		if (sql.indexOf("insert ") >= 0 || sql.indexOf("update ") >= 0 || sql.indexOf("delete ") >= 0)
		{
			context.addMessage(null, new FacesMessage(eFailTitle, "只支持查询语句，不允许更新!"));
			return;
		}
		
		// 如果是select 必须增加limit限制
		if (sql.indexOf("select ") >= 0 && sql.indexOf("limit ") < 0)
		{
			if(sql.endsWith(";")){
				sql = sql.substring(0, sql.length()-1) + " limit 1000 ;";
			}else{
				sql = sql + " limit 1000 ;";
			}
		}
		// 执行sql
		try
		{
			Map.Entry<List<String>, List<Object[]>> ret = hadoopQueryManager.executeSQL(sql);
			if (null == ret || null == ret.getKey() || ret.getKey().isEmpty() || null == ret.getValue())
			{
				context.addMessage(null, new FacesMessage(eFailTitle, "查询结果为空!"));
				return;
			}
			this.hasData = true;
			this.columnNames = ret.getKey();
			this.dataList = ret.getValue();
			sysLogManager.addLog(session.getUserName(), "查询执行成功,sql=" + sql);
		}
		catch (Exception ex)
		{
			com.loongtech.bi.support.Log.bi.error("SqlQueryAction search sql={} ex={}", sql, ex);
			context.addMessage(null, new FacesMessage(eFailTitle, "sql execute Exception " + ex));
		}
	}
	
	public String getSql()
	{
		return sql;
	}

	public void setSql(String sql)
	{
		this.sql = sql;
	}

	public boolean isHasData()
	{
		return hasData;
	}

	public void setHasData(boolean hasData)
	{
		this.hasData = hasData;
	}

	public List<Object[]> getDataList()
	{
		return dataList;
	}

	public void setDataList(List<Object[]> dataList)
	{
		this.dataList = dataList;
	}

	public List<String> getColumnNames()
	{
		return columnNames;
	}

	public void setColumnNames(List<String> columnNames)
	{
		this.columnNames = columnNames;
	}

	@Override
	protected String csvContent()
	{
		if (null == columnNames || columnNames.isEmpty())
		{
			return "";
		}
		// 写输出文件头部
		StringBuffer sb = new StringBuffer();
		for (String columnName : columnNames)
		{
			sb.append(columnName).append(",");
		}
		sb.append("\n");
		// 内容
		for (Object[] item : dataList)
		{
			if (null == item || item.length == 0)
			{
				continue;
			}
			for (Object obj : item)
			{
				if (null != obj)
				{
					sb.append(obj.toString()).append(",");
				}
				else
				{
					sb.append(",");
				}
			}
			sb.append("\n");
		}
		return sb.toString();
	}

	protected String csvFileName()
	{
		return "通用查询结果";
	}
}
